package controls.sql
{
	import data.ClassSection;
	import data.Course;
	import data.Degree;
	import data.DegreeType;
	import data.Professor;
	import data.User;
	
	public class SqlStatements
	{	
		// ------------------ Public Static Variables ---------------- //
		public static var DEGREE_TABLE:String = "degree";
		public static var DEGREE_PLAN_TABLE:String = "degreeplan";
		public static var CLASS_TABLE:String = "courses";
		public static var PRE_REQS_TABLE:String = "prereqs";
		public static var CO_REQS_TABLE:String = "coreqs";
		public static var GROUP_REQS:String = "groupreqs";
		public static var COURSE_CRITIQUE:String = "coursecritique";
		public static var OSCAR:String = "oscar";
		public static var PROFESSOR_GPA:String = "profgpa";
		public static var USERS:String = "users";
		// ------------------ END Public Static Variables ---------------- //
		
		// --------------- SQL Generating Functions ----------------- //
		public function checkUser(username:String, password:String):String
		{
			return "SELECT * FROM " + USERS; 
		}
		
		public function updateUser(user:User):String
		{
			var degreeString:String = " ";
			for (var degreeType:String in user.degrees)
				for each(var degree:Degree in user.degrees[degreeType])
					degreeString += degree.id + " ";
			
			var courseString:String = " ";
			for each(var course:Course in user.coursesTaken)
				courseString += course.id + " ";
					
			return "UPDATE " + USERS + " SET "
						+ " 'degree' = '" + degreeString + "', '"
						+ " 'courseWork' = '" + courseString + "'" 
					+ " WHERE 'userName' = '" + user.username + "'";
		}
		
		public function addUser(user:User):String
		{
			var degreeString:String = " ";
			for (var degreeType:String in user.degrees)
				for each(var degree:Degree in user.degrees[degreeType])
					degreeString += degree.id + " ";
			
			var courseString:String = " ";
			for each(var course:Course in user.coursesTaken)
				courseString += course.id + " ";
			
			return "INSERT INTO " + USERS 
				+ " ( userName, password, degree, courseWork)"
				+ " VALUES ( '" + user.username + "', '" 
								+ user.password + "', '" 
								+ degreeString + "', '"
								+ courseString + "')";
		}
		
		public function getCourseSections(course:Course):String
		{
			return "SELECT * FROM " + OSCAR 
				+ " WHERE CONCAT(SUBJECT, COURSE) = '" + course.title + "'";
		}
		
		public function getClass(classId:String):String
		{
			return "SELECT * FROM " + CLASS_TABLE
					+ " LEFT JOIN " + PRE_REQS_TABLE 
						+ " ON " + CLASS_TABLE + ".UID = " + PRE_REQS_TABLE + ".course"
					+ " LEFT JOIN " + CO_REQS_TABLE 
						+ " ON " + CLASS_TABLE + ".UID = " + CO_REQS_TABLE + ".course"
					+ " WHERE " + CLASS_TABLE + ".UID = '" + classId + "'";
		}
		
		public function getClassGroup(groupId:Object):String
		{
			return "SELECT * FROM " + CLASS_TABLE
					+ " JOIN " + GROUP_REQS 
						+ " ON " + CLASS_TABLE + ".UID = " + GROUP_REQS + ".CourseID"
					+ " WHERE " + GROUP_REQS + ".GroupID = '" + groupId + "'";
		}
		
		public function getCourseGPA(course:Course):String
		{
			return "SELECT * FROM " + COURSE_CRITIQUE 
					+ " WHERE " + COURSE_CRITIQUE + ".CourseID = '" + course.title + "'";
		}
		
		public function getSectionGPA(section:ClassSection):String
		{
			return "SELECT * FROM " + COURSE_CRITIQUE
					+ " WHERE " + COURSE_CRITIQUE + ".CRN = '" + section.CRN + "'"
					+ " AND " + COURSE_CRITIQUE + ".Section = '" + section.section + "'";
		}
		
		public function getProfessorGPA(professor:Professor):String
		{
			return 'SELECT * FROM ' + PROFESSOR_GPA
					+ ' WHERE ProfLastName = "'  + professor.lastName + '"'
					+ ' AND ProfFirstInitial = "' + professor.firstName.charAt(0) + '"';
		}
		
		public function getProfessorCourseGPA(professor:Professor, title:String):String
		{
			return 'SELECT * FROM ' + COURSE_CRITIQUE
					+ ' WHERE ' + COURSE_CRITIQUE + '.CourseID = "' + title + '"'
					+ ' AND ' + COURSE_CRITIQUE + '.NameLast = "' + professor.lastName + '"';
		}
		
		public function getDegree(degree:Degree):String
		{
			return getDegreeById(degree.id);
		}
		
		public function getDegreeById(degreeId:int):String
		{
			return "SELECT * FROM " + DEGREE_PLAN_TABLE 
					+ " WHERE degreeID = '" + degreeId + "'";
		}
		
		public function getMajors():String
		{
			return "SELECT * FROM " + DEGREE_TABLE
					+ " WHERE type = '" + DegreeType.MAJOR + "'";
		}
		
		public function getMinors():String
		{
			return "SELECT * FROM " + DEGREE_TABLE
					+ " WHERE type = '" + DegreeType.MINOR + "'";
		}
		
		public function getCertificates():String
		{
			return "SELECT * FROM " + DEGREE_TABLE
					+ " WHERE type = '" + DegreeType.CERTIFICATE + "'";
		}
		// --------------- SQL Generating Functions ----------------- //
	}
}